<?php

/**
 *  General database related functions
 *
 *  This collection of functions (libary) is licensed under GPL2.
 *  See LICENSE or www.gnu.org for more details.
 *  @author Henri Ranki  4.9.2002
 */

include_once("sodebug.php");
include_once("soset.php");
include_once("soarrayset.php");
include_once("sostring.php");
include_once("sosqlhi.php");


/** Define error values for soCSVexport class*/
define(SO_CSV_ERROR_NO_KEYS, 1);
define(SO_CSV_ERROR_NO_COLS, 2);
define(SO_CSV_ERROR_NO_TABLES, 3);
define(SO_CSV_ERROR_QUERY, 4);

/* Define export types for soCSVexport class. */
define(SOEXP_CSV, "csv");
define(SOEXP_TAB, "tab");


/** Export data from database in text format.
 *
 * Usege example:
 *
 * $dbaccess = "driver=pg trimming=1 incase=1 date=j.4 host=saku database=anonyymi username=postgres password=";
 *
 * // create table structure
 * $tables = array("kysymykset" => array("id:int"),
 *                 "otsikot" => array("id:int"));
 *
 *  // set key values
 * $key_vals = array(array(11), array(12), array(13), array(14));
 *
 * $result = soExport($dbaccess,
 *               SOEXP_TAB,
 *               "<dztab>", "\n",
 *               "kysymykset.aika:datetime kysymykset.id:int otsikot.otsikko",
 *               $tables,
 *               $key_vals);
 *
 * if ($result != NULL) {
 *     echo $result."\n";
 * } else {
 *     echo "Export failed!\n";
 * } // if
 *
 *  @param DZEXP_* $Type Export format type
 *  @param string $SpecialChar String that should replace reserved
 *      characters in conversion. In CSV export, replaces " character
 *      inside quotes and \t in TAB export.
 *  @param string $NewLine Char/string to start a new line.
 *  @param string $DataCols Space delimited list of data columns. Each
 *      column name is in form "table.name:datatype" (text type by default")
 *  @param array/array $Tables Table structure: $Tables["tablename"] ==
 *      array("keyname:datatype, "next key...")
 *  @param array/array $Keys Key values: $Keys[0] = array(1,2) etc.
 *  @param int Progress output: 0=no output, 1=print dot for each row
 *      exported, 2=print key value for each row exported.
 *  @param array/array $Substitutes array[0] contains strings that should be
 *      replaced with string in array[1]. For example, 
 *      array(array("\n", "\r"), array(" ", " ")) would cause all \n and \c
 *      characters to be replaced with " ".
 */
function soExport($DbAccess, $Type, $SpecialChar, $NewLine = "\n",
                   $DataCols, $Tables, $Keys, $Progress = 0,
                   $Substitutes = NULL) {

    $exporter = new soCSVexport($DbAccess);
    $exporter->setCols($DataCols);

    $exporter->setKeys($Tables, $Keys);
    $exporter->setType($Type);
    if ($NewLine != "") {
        $exporter->RowDelimiter = $NewLine;
    } // if
    $exporter->SpecialChar = $SpecialChar;
    $exporter->Substitutes = $Substitutes;

    return $exporter->export($Progress);
} // dzExport



 /** Class to import data from database
 * Data is imported as string where columns and rows are delimited
 * with user specified delimiters. Defaults are: column delimites "/t"
 * and row delimiter "/n".
 *
 */
class soCSVexport extends soBaseClass {
    var $__Type;    /** SOEXP_* Export type */
    var $__Soda;    /** soDa Soda used to query data from db */
    var $__Cols = NULL; /**array/str A space delimited list of columns
                             to export per table. columns are in format:
                             table.column:type*/
    var $__Tables = null; /**array/array $Tables Table structure: $Tables["tablename"]
                            array("key_1_name:datatype", "key_1_name:datatype") */
    var $__Keys = NULL; /**array/array  Key values: $Keys[0] = array(1,2) etc. */
    var $__KeyColCount; /** Amount of key cols */

    var $Substitutes;         /** Substitute strings */
    var $ColDelimiter = "\t"; /**string delimiter used to separate columns */
    var $RowDelimiter = "\n"; /**string delimiter used to separate rows */
    var $__ResultSet;   /**soArraySet Set containing the values for each column */
    var $SpecialChar = ""; /**str String that should replace reserved
                                characters in conversion. In CSV export,
                                replaces " character inside quotes
                                and \t in TAB export. */


    /** Constructor for the class
     * @param string $DbAccess The db access string
     */
    function soCSVexport($DbAccess) {
        $this->__Soda = new soDa($DbAccess);
    } // soCSVexport

    /** Set the export type
     * Sets the type whether TAB or CSV. The only difference is
     * the character that separates columns. (default is TAB)
     * @param str $Type The type to set
     */
    function setType($Type) {
        $this->__Type = $Type;
        switch ($Type) {
            case SOEXP_CSV:
                $this->ColDelimiter = ",";
                break;
            default:
                $this->ColDelimiter = "\t";
        } // switch
    } // setType


    /** Set tables and cols where the data is exported from.
     * @param string $Cols Table and column names. Names are in format: table_name.col_name
     *               Table-column pairs are separated with space or a comma.
     */
    function setCols($Cols) {
        $Cols = soSwapStr($Cols, " ", ",");
        $col_array = soExplode($Cols, ",");
        /* Reset the column list */
        $this->__Cols = array();

        foreach ($col_array as $col_name) {
            $table_name = soExtractVal($col_name, ".", 1);
            /* Add the column to the list of exported columns */
            $table_cols = $this->__Cols[$table_name];
            if ($table_cols == NULL) $table_cols = "";
            $table_cols = soJoinStr($table_cols, " ", $col_name);
            $this->__Cols[$table_name] = $table_cols;
        } // foraeach
    } // setCols


    /** Set keys and their values
     *
     *  @param array/array $Tables array/array $Tables Table structure:
     *                  $Tables["tablename"] = array("key_1_name:datatype",
     *                                              "key_1_name:datatype")
     *  @param array/array/any $Keys Key values: $Keys[0] = array(1,2) etc.
     *  @return bool true if ok otherwise false
     */
    function setKeys($Tables, $Keys) {
        /* Check parameters */
        $ccount = -1;
        $result = true;
        foreach ($Tables as $table => $cols) {
            if ($ccount < 0) {
                $ccount = count($cols);
            } else {
                if ($ccount != count($cols)) {
                    $result = false;
                    soDebug("soCSVexport error: Key col count in ".
                            "tables not match", SOD_HI_E);
                    break;
                } // if
            }
        } // foreach

        /* Store parameters */
        $this->__Tables = $Tables;
        $this->__Keys = $Keys;
        $this->__KeyColCount = $ccount;

        return $result;
    } // setKeys


    /** Export the values
     *  @param int Progress output: 0=no output, 1=print dot for each row
     *      exported, 2=print key value for each row exported.
     * @return string the exported. NULL if error.
     */
    function export($Progress = 0) {
        /* Check that all required parameters are given */
        if ($this->__Keys == NULL) {
            $this->Error = SO_CSV_ERROR_NO_KEYS;
            return NULL;                    // <-- EXIT!!
        } // if

        if ($this->__Cols == NULL) {
            $this->Error = SO_CSV_ERROR_NO_COLS;
            return NULL;                    // <-- EXIT!!
        } // if
        if ($this->__Tables == NULL) {
            $this->Error = SO_CSV_ERROR_NO_TABLES;
            return NULL;                    // <-- EXIT!!
        } // if

        /* Start querying values */
        $this->__ResultSet = new soArraySet();

        /* Set constant parameters fo soDa and clear any existing tables
         * and solumns */
        $this->__Soda->setAction(SODA_SELECT);
        $this->__Soda->setTable("", 1);
        $this->__Soda->clearAllSections();

        $table_no = 0;
        foreach ($this->__Tables as $table_name => $key_cols) {
            $this->__Soda->setTable($table_name);

            /* Set data cols */
            $req_cols = soExplode($this->__Cols[$table_name], " ");
            if (!$table_no) $master_cols = $req_cols;

            foreach ($req_cols as $req_col) {
                $this->__Soda->addToSection(SODA_DATA, $req_col, NULL);
            }; // foreach
            $kcount = count($key_cols) -1;

            /* Set where cols & values */
            $key_col1 = "$table_name.".
                        soExtractVal($key_cols[0], ":", 1)."#0";
            $icount = count($this->__Keys);
            if (!$table_no) {
                for ($i = 0; $i < $icount; $i++) {
                    $row_keys = $this->__Keys[$i];
                    if (count($row_keys) != $this->__KeyColCount) {
                        soDebug("soCSVexport->export: key value amount mismatch.".
                                " Should be ".$this->__KeyColCount, SOD_E_HI);
                        return NULL;
                    } // if

                    for ($k = 0; $k <= $kcount; $k++) {
                        $key_col = soExtractVal($key_cols[$k], ":", 1)."#$i";
                        $dtype = soExtractVal($key_cols[$k], ":", 2);
                        if ($dtype) $key_col .= ":$dtype";
                        $this->__Soda->addToSection(SODA_WHERE,
                                                    "$table_name.".
                                                    $key_col,
                                                    $row_keys[$k]);
                    }; // for
                    $key_col2 = "$table_name.".
                                soExtractVal($key_cols[$kcount], ":", 1).
                                "#$i";
                    if ($i < ($icount -1)) {
                        $this->__Soda->addToSection(SODA_WHERE_ORS,
                                                    $key_col2, 1);
                    }; // if
                } // for $i
                $this->__Soda->addToSection(SODA_PARENTHESES,
                                            $key_col1, "1,0");
                $this->__Soda->addToSection(SODA_PARENTHESES,
                                            $key_col2, "0,1");
            } else {
                for ($k = 0; $k <= $kcount; $k++) {
                    $col = $key_cols[$k];
                    $value = SO_DOLLAR.soExtractVal($master_cols[$k], ":", 1);
                    $this->__Soda->addToSection(SODA_WHERE,
                                                "$table_name.$col",
                                                $value);
                };
            }; // else if
            $table_no++;
        }; // foreach table

        /* Run query */
        if ($this->__Soda->run()) {
            $results = $this->__Soda->Results;
        } else {
            $this->Error = SO_CSV_ERROR_QUERY;
            return NULL;                    // <-- EXIT!!!
        } // if

        /* Store the result if the query was ok. */
        $icount = $results->count();
        for ($i = 0; $i < $icount; $i++) {
            foreach ($results->getColNames() as $col_name) {
                $val = $results->getValue($col_name, $i);
                if ($this->Substitutes != NULL) {
                    $val = soSwapAll($val, $this->Substitutes[0],
                                    $this->Substitutes[1]);
                }; // if

                $this->__ResultSet->setArrayItem($col_name, $i, $val);
            }; // foreach $this->__Keys
            if ($i % 5 == 0) {
                echo ". ";
                flush();
            }; // if
        }; // for

        return $this->__getResultStr();
    } // export


    /** dumps the result columns and uses the specified delimiters
     * @return str  The result string
     */
    function __getResultStr() {
        $cols = $this->__ResultSet->getKeys();
        $rcount = $this->__ResultSet->getRowCount($cols, 1);
        $result = "";

        for ($c = 0; $c < $rcount; $c++) {
            $row = $this->__ResultSet->getRow($c);
            if ($this->__Type == SOEXP_CSV) $row = soArrayAddQuotes($row);
            $ccount = count ($row);
            for ($i = 0; $i < $ccount; $i++) {
                $row[$i] = soSwapStr($row[$i],
                                     $this->ColDelimiter,
                                     $this->SpecialChar);
            } // for
            $result = soJoinStr($result,
                                $this->RowDelimiter,
                                implode($this->ColDelimiter, $row));

        } // for

        return $result;
    } // getResultStr

} // soCSVexport


?>
